import plotly.express as px
import pandas as pd
import plotly.graph_objects as go
import json
import sqlite3
from shapely.geometry import Point
from shapely.geometry.polygon import Polygon
import plotly.io as pio
pio.renderers.default='notebook'

# Connecting to SQLite database and pulling aggregated speed and red light camera data
connection = sqlite3.connect("camera_violations.db")
c = connection.cursor()

s = ''' select ADDRESS, CAMERAID, sum(VIOLATIONS) AS 'TOTAL_VIOLATIONS', LATITUDE, LONGITUDE from speed group by CAMERAID '''
c.execute(s)
table = c.fetchall()

df = pd.DataFrame(table)
df = df.rename(columns = {0:'address',1:'camera_id',2:'violations',3:'lat',4:'lon'})

s2 = ''' SELECT INTERSECTION, CAMERAID, ADDRESS, SUM(VIOLATIONS) as 'TOTAL_VIOLATIONS', LATITUDE, LONGITUDE FROM red_light group by ADDRESS '''
c.execute(s2)
table2 = c.fetchall()

df2 = pd.DataFrame(table2)
df2 = df2.rename(columns = {0:'intersection',1:'camera_id', 2:'address',3:'violations',4:'lat',5:'lon'})

# Loading Census Tract and Race data
f = open("chi_census.geojson")
tracts = json.load(f)
race = pd.read_csv("race_by_tract.csv")

# Setting reference size for scatter bubbles
sizeref_s = 2. * df['violations'].max() / (8 ** 2)
sizeref_r = 2. * df2['violations'].max() / (8 ** 2)

# Creating Choropleth map
fig = go.Figure(go.Choroplethmapbox(geojson=tracts, locations=race['Tract_name'],
                                featureidkey='properties.namelsad10', \
                                z= race['Non-White'], \
                                colorscale="Inferno_r", \
                                text=race['Non-White'],
                                zmin=0.0, zmax=1.2, #text=zhvi_county_inc_pop["text_2yrs"], 
                                marker_opacity=0.5, marker_line_width=0))
fig.update_layout(mapbox_style="carto-positron",
                mapbox_zoom=10, mapbox_center = {"lat": 41.9, "lon": -87.6})

fig.add_trace(go.Scattermapbox(
            lon = df['lon'],
            lat = df['lat'],
            mode = 'markers',
            marker = dict(
                size = df['violations']/sizeref_s,
                color = 'blue'
                    )))
fig.add_trace(go.Scattermapbox(
            lon = df2['lon'],
            lat = df2['lat'],
            mode = 'markers',
            marker = dict(
                size = df2['violations']/sizeref_r,
                color = 'red'
                    )))
       
        

fig.show()

# Creating a list of polygon objects for all census tracts
polsd = {}
for x in tracts['features']:
    pol = Polygon(x['geometry']['coordinates'][0][0])
    polsd[x['properties']['namelsad10']] = pol

# Creating lists of point objects for all speed and red light cameras
speed_points = []
for r in df.itertuples():
    if r[4] and r[5]:
        loc = (float(r[5]),float(r[4]))
        speed_points.append(Point(loc))
rl_points = []
for r in df2.itertuples():
    if r[5] and r[6]:
        loc = (float(r[6]),float(r[5]))
        rl_points.append(Point(loc))

# Functions to return numbers of speed/red light cameras in a given polygon
def num_speed_cams_in_poly(pol_name):
    s_cams = 0
    for p in speed_points:
        if pol_name in polsd and polsd[pol_name].contains(p):
            s_cams += 1
    return s_cams
def num_rl_cams_in_poly(pol_name):
    r_cams = 0
    for p in rl_points:
        if pol_name in polsd and polsd[pol_name].contains(p):
            r_cams += 1
    return r_cams

# Adding number of cameras to the race dataframe, as this is already broken out by census tract
race['num_speed_cams'] = race.apply(lambda row: num_speed_cams_in_poly(row['Tract_name']), axis = 1)
race['num_rl_cams'] = race.apply(lambda row: num_rl_cams_in_poly(row['Tract_name']), axis = 1)
race['all_cams'] = race['num_speed_cams'] + race['num_rl_cams']
race = race.replace("#DIV/0!", '0')
race['Non-White'] = race['Non-White'].astype(float)